﻿using SupermarketSalesModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SupermarketSalesController
{
    public class SQLServerDAOClient : DAOClient
    {
        public void add(Client c)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO G3_CLIENT(name, lastName, bornDate," +
                    "idCard, registerDate, telephoneNumber, email, status, cardNumber, category, points)" +
                    "VALUES (@param1,@param2,@param3,@param4,@param5,@param6,@param7,@param8,@param9,@param10,@param11)";
                SqlParameter param1 = new SqlParameter("@param1",
                                                         System.Data.SqlDbType.VarChar);
                SqlParameter param2 = new SqlParameter("@param2",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param3 = new SqlParameter("@param3",
                                                        System.Data.SqlDbType.DateTime);
                SqlParameter param4 = new SqlParameter("@param4",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param5 = new SqlParameter("@param5",
                                                        System.Data.SqlDbType.DateTime);
                SqlParameter param6 = new SqlParameter("@param6",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param7 = new SqlParameter("@param7",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param8 = new SqlParameter("@param8",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param9 = new SqlParameter("@param9",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param10 = new SqlParameter("@param10",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param11 = new SqlParameter("@param11",
                                                        System.Data.SqlDbType.Int);

                param1.Value = c.Name;
                param2.Value = c.Lastname;
                param3.Value = c.BornDate;
                param4.Value = c.IdCard;
                param5.Value = c.RegisterDate;
                param6.Value = c.TelephoneNumber;
                param7.Value = c.Email;
                param8.Value = c.Status;
                param9.Value = c.CardNumber;
                param10.Value = c.Category;
                param11.Value = c.Points;

                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);
                comm.Parameters.Add(param3);
                comm.Parameters.Add(param4);
                comm.Parameters.Add(param5);
                comm.Parameters.Add(param6);
                comm.Parameters.Add(param7);
                comm.Parameters.Add(param8);
                comm.Parameters.Add(param9);
                comm.Parameters.Add(param10);
                comm.Parameters.Add(param11);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }

        public void update(Client c)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "UPDATE G3_CLIENT SET name=@param1, lastName=@param2," +
                        "bornDate=@param3, idCard=@param4, registerDate=@param5, telephoneNumber=@param6, email=@param7,"+
                        "status=@param8, cardNumber=@param9, telephoneNumber=@param10, telephoneNumber=@param11 WHERE idClient=@param12";
                SqlParameter param1 = new SqlParameter("@param1",
                                                         System.Data.SqlDbType.VarChar);
                SqlParameter param2 = new SqlParameter("@param2",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param3 = new SqlParameter("@param3",
                                                        System.Data.SqlDbType.DateTime);
                SqlParameter param4 = new SqlParameter("@param4",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param5 = new SqlParameter("@param5",
                                                        System.Data.SqlDbType.DateTime);
                SqlParameter param6 = new SqlParameter("@param6",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param7 = new SqlParameter("@param7",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param8 = new SqlParameter("@param8",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param9 = new SqlParameter("@param9",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param10 = new SqlParameter("@param10",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param11 = new SqlParameter("@param11",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param12 = new SqlParameter("@param12",
                                                        System.Data.SqlDbType.Int);

                param1.Value = c.Name;
                param2.Value = c.Lastname;
                param3.Value = c.BornDate;
                param4.Value = c.IdCard;
                param5.Value = c.RegisterDate;
                param6.Value = c.TelephoneNumber;
                param7.Value = c.Email;
                param8.Value = c.Status;
                param9.Value = c.CardNumber;
                param10.Value = c.Category;
                param11.Value = c.Points;
                param12.Value = c.IdClient;

                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);
                comm.Parameters.Add(param3);
                comm.Parameters.Add(param4);
                comm.Parameters.Add(param5);
                comm.Parameters.Add(param6);
                comm.Parameters.Add(param7);
                comm.Parameters.Add(param8);
                comm.Parameters.Add(param9);
                comm.Parameters.Add(param10);
                comm.Parameters.Add(param11);
                comm.Parameters.Add(param12);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }

        public void delete(String idClient)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "DELETE FROM G3_CLIENT " +
                                            " WHERE idClient=@param1";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.VarChar);
                param1.Value = idClient;

                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }

        public List<Client> queryAll()
        {
            List<Client> list = new List<Client>();
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM G3_CLIENT";

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                while (myReader.Read())
                {
                    Client c = new Client();
                    c.IdClient = Int32.Parse(myReader["idClient"].ToString());
                    c.Name = myReader["name"].ToString();
                    c.Lastname = myReader["lastName"].ToString();
                    c.BornDate = DateTime.Parse(myReader["bornDate"].ToString());
                    c.IdCard = myReader["idCard"].ToString();
                    c.RegisterDate = DateTime.Parse(myReader["registerDate"].ToString());
                    c.TelephoneNumber = myReader["telephoneNumber"].ToString();
                    c.Email = myReader["email"].ToString();
                    c.Status = Int32.Parse(myReader["status"].ToString());
                    c.CardNumber = myReader["cardNumber"].ToString();
                    c.Category = myReader["category"].ToString();
                    c.Points = Int32.Parse(myReader["points"].ToString());

                    list.Add(c);
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 6. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return list;
        }

        public Client queryByIdCard(String idClient)
        {
            Client c = null;
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM G3_CLIENT WHERE idClient=@param1";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.VarChar);
                param1.Value = idClient;
                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                if (myReader.Read())
                {
                    c = new Client();
                    c.IdClient = Int32.Parse(myReader["idClient"].ToString());
                    c.Name = myReader["name"].ToString();
                    c.Lastname = myReader["lastName"].ToString();
                    c.BornDate = DateTime.Parse(myReader["bornDate"].ToString());
                    c.IdCard = myReader["idCard"].ToString();
                    c.RegisterDate = DateTime.Parse(myReader["registerDate"].ToString());
                    c.TelephoneNumber = myReader["telephoneNumber"].ToString();
                    c.Email = myReader["email"].ToString();
                    c.Status = Int32.Parse(myReader["status"].ToString());
                    c.CardNumber = myReader["cardNumber"].ToString();
                    c.Category = myReader["category"].ToString();
                    c.Points = Int32.Parse(myReader["points"].ToString());
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return c;
        }


        public Client queryById(int idClient)
        {
            Client c = null;
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM G3_CLIENT WHERE idClient=@param1";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                param1.Value = idClient;
                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                if (myReader.Read())
                {
                    c = new Client();
                    c.IdClient = Int32.Parse(myReader["idClient"].ToString());
                    c.Name = myReader["name"].ToString();
                    c.Lastname = myReader["lastName"].ToString();
                    c.BornDate = DateTime.Parse(myReader["bornDate"].ToString());
                    c.IdCard = myReader["idCard"].ToString();
                    c.RegisterDate = DateTime.Parse(myReader["registerDate"].ToString());
                    c.TelephoneNumber = myReader["telephoneNumber"].ToString();
                    c.Email = myReader["email"].ToString();
                    c.Status = Int32.Parse(myReader["status"].ToString());
                    c.CardNumber = myReader["cardNumber"].ToString();
                    c.Category = myReader["category"].ToString();
                    c.Points = Int32.Parse(myReader["points"].ToString());
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return c;
        }


        public int count()
        {
            int quantity = 0;
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Count(1) AS quantity FROM G3_CLIENT";
                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                if (myReader.Read())
                {
                    quantity = Int32.Parse(myReader["quantity"].ToString());
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return quantity;
        }
    }
}
